package gui;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.format.CellDateFormatter;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.swt.widgets.Table;
import org.apache.xmlbeans.XmlOptions;
import common.MyDate;
import  org.dom4j.DocumentException;

/**
 * class ReadWriteFile
 * reads XLS / XLSX files, and parse them into an array.
 * writes info into XLS file.
 * @author michal
 *
 */
public class ReadWriteFile {
	private String path;	//path of file chosen by user
	private ArrayList<ArrayList<String>> fileContent;	//excel file into an array
	private Sheet sheet;	//sheet of excel file
	private FileInputStream fis;	//input stream
	private Workbook workbook;	//excel's workbook

	/**
	 * c-tor
	 * gets a path of file and initializes fileContent
	 * @param path
	 */
	public ReadWriteFile(String path) {
		super();
		this.path = path;
		fileContent = new ArrayList<ArrayList<String>>();
	}
	private void openFile() throws InvalidFormatException{
		fis = null;
		try {
			// Create a FileInputStream that will be use to read the
			// excel file.
			fis = new FileInputStream(path);
			// Create an excel workbook from the file system.
			workbook = WorkbookFactory.create(fis);
			// Get the first sheet on the workbook.
			sheet = workbook.getSheetAt(0);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fis != null) {
				try {
					fis.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * returns the excel file content in a ArrayList<ArrayList<String>> object
	 * @return
	 * @throws InvalidFormatException
	 */
	public ArrayList<ArrayList<String>> getFileContent() throws InvalidFormatException{		
		//open file
		openFile();
		// When we have a sheet object in hand we can iterator on
		// each sheet's rows and on each row's cells. We store the
		// data read on an ArrayList so that we can printed the
		// content of the excel to the console.
		Iterator<Row> rows = sheet.rowIterator();
		int size=rows.next().getLastCellNum();	//number of cells
		Date date;	//date object for date cells content
		String dateFmt;
		String cellValue = null;
		Row row;
		ArrayList<String> data;
		while (rows.hasNext()) {
			row =  rows.next();	//current row
			data = new ArrayList<String>();

			for (int i=0;i<size;i++){	//iterating the cells in current row
				//getting the cell
				org.apache.poi.ss.usermodel.Cell cell =  row.getCell(i,Row.CREATE_NULL_AS_BLANK);
				//check if the cell's content is date
				if (cell.getCellType()==org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC&&
						DateUtil.isCellDateFormatted(cell)){
					date = DateUtil.getJavaDate(cell.getNumericCellValue());
					dateFmt = cell.getCellStyle().getDataFormatString();
					dateFmt="dd/MM/yyyy";
					cellValue = new CellDateFormatter(dateFmt).format(date); 
				}else{	//string cell
					cell.setCellType(org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING);
					cellValue = cell.toString();
				}
				//adding the cell to current row
				data.add(cellValue);
			}				
			fileContent.add(data);
		}

		//close input stream
		if (fis != null) {
			try {
				fis.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	/**
	 * method gets the column indexes, and returns an array containing
	 * only these columns
	 * @param colNumbers
	 * @return
	 */
	public ArrayList<ArrayList<Cell>> createCandidatesInput(int[] colNumbers){
		ArrayList<ArrayList<Cell>> clients = new ArrayList<ArrayList<Cell>>();
		ArrayList<String> row;
		//special case - giving tasks table
		if (colNumbers==null){
			return createGivingTasksInput();
		}

		//iterating the file's rows
		for (int i=0;i<fileContent.size();i++){
			row = fileContent.get(i);
			ArrayList<Cell> client = new ArrayList<Cell>();
			for (int j=0;j<colNumbers.length;j++){ //iterating columns
				if (colNumbers[j]<0)	//insert an empty cell
					client.add(new Cell(null, ""));
				else	//insert current cell
					client.add(new Cell(null, row.get(colNumbers[j])));
			}
			clients.add(client);
		}
		return clients;
	}

	/**
	 * method unique for giving tasks table input.
	 * gets the column indexes, and returns an array containing
	 * only these columns
	 * @return
	 */
	private ArrayList<ArrayList<Cell>> createGivingTasksInput() {
		ArrayList<ArrayList<Cell>> clients = new ArrayList<ArrayList<Cell>>();
		ArrayList<String> row;
		if (fileContent.isEmpty())
			return clients;
		String lastClient="";
		String currentTask;
		int colNumbers = fileContent.get(0).size();
		ArrayList<Cell> client;
		
		for (int i=0;i<fileContent.size();i++){
			row = fileContent.get(i);
			client = new ArrayList<Cell>();
			//id column is missing in this document
			client.add(new Cell(null, ""));
			if (row.get(0).length()>0)
				lastClient=row.get(0);
			client.add(new Cell(null, lastClient));
			for (int j=1;j<colNumbers;j++){
				currentTask=row.get(j);				
				client.add(new Cell(null, currentTask));
			}
			Collections.reverse(client);
			clients.add(client);
		}
		return clients;
	}
	
	/**
	 * method creates a new XLS file.
	 */
	public void createFile(){
		try {
			FileOutputStream fileOut = new FileOutputStream(path);
			workbook.write(fileOut);
			fileOut.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * method gets a table and writes its content into an excel file.
	 * @param table
	 */
	public void writeToFile(Table table){
		workbook = new HSSFWorkbook();
		sheet = workbook.createSheet();
		sheet.setRightToLeft(true);

		int numberOfRows = table.getItemCount();
		int numberOfCols = table.getColumnCount();
		HSSFRow row;
		HSSFCell cell;
		/*get columns names*/
		row = (HSSFRow) sheet.createRow(0);
		for (int i=0;i<numberOfCols;i++){
			cell = row.createCell(i);
			cell.setCellValue(table.getColumn(i).getText());
		}
		/*get all other rows*/
		for (int i=0;i<numberOfRows;i++){
			row = (HSSFRow) sheet.createRow(i+1);
			for (int j=0;j<numberOfCols;j++){
				cell = row.createCell(j);
				cell.setCellValue(table.getItem(i).getText(j));
			}
		}
		createFile();
	}
	public ArrayList<ArrayList<Cell>> createCentralInput(int[] colNumbers) {
		ArrayList<ArrayList<Cell>> clients = new ArrayList<ArrayList<Cell>>();
		ArrayList<String> row;
		String centralNumber;
		int indexOfLastSlash;
		int indexOfLastDot;
		indexOfLastSlash=path.lastIndexOf('/');
		if (indexOfLastSlash<0)
			indexOfLastSlash=path.lastIndexOf('\\');
		indexOfLastDot = path.lastIndexOf('.');
		centralNumber = path.substring(indexOfLastSlash+1,indexOfLastDot);
		
		//special case - giving tasks table
		if (colNumbers==null){
			return createGivingTasksInput();
		}

		//iterating the file's rows
		for (int i=0;i<fileContent.size();i++){
			row = fileContent.get(i);
			ArrayList<Cell> client = new ArrayList<Cell>();
			for (int j=0;j<colNumbers.length;j++){ //iterating columns
				if (colNumbers[j]==9)	//insert an empty cell
					client.add(new Cell(null, row.get(8)+" "+row.get(9)));
				else if (colNumbers[j]==16)	//NOTES cell - put the cellphone and the filename
					client.add(new Cell(null, centralNumber+" , "+row.get(7)));
				else//insert current cell
					client.add(new Cell(null, row.get(colNumbers[j])));
			}
			clients.add(client);
		}
		return clients;
	}
}
